package com.ynitil.utils;

import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.jfinal.plugin.activerecord.Record;
/**
 * @描述: 类说明描述 : jdbc连接 
 * @创建人: liuxinpeng
 * @创建时间: 2017-8-22 下午06:23:58
 */


public class JdbcDao {
	public boolean loginStatus = false;
	public Connection conn = null;
	public String error = null;
	
	/**
	 * @描述: 登录mysql数据库 
	 * @param ip
	 * @param port
	 * @param dbname
	 * @param user
	 * @param pwd
	 * @return 登录状态
	 */
	public boolean loginMySQL(String ip, int port, String dbname, String user, String pwd) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			this.conn = DriverManager.getConnection("jdbc:mysql://" + ip + ":" + port + "/" + dbname + "?characterEncoding=UTF8", user, pwd);
			loginStatus = true;
			error=null;
		} catch (Exception e) {
			e.printStackTrace();
			loginStatus = false;
			error = "MySQL登录异常。原因:" + e.getMessage();
		}
		return loginStatus;
	}
	
	/**
	 * 
	 * @描述: 登录pgsql数据库 
	 * @param ip
	 * @param port
	 * @param dbname
	 * @param user
	 * @param pwd
	 * @return 登录状态
	 */
	public boolean loginPgSQL(String ip, int port, String dbName, String user, String pwd) {
		try {
			Class.forName("org.postgresql.Driver");
			String url = "jdbc:postgresql://" + ip + ":" + port + "/" + dbName;
			this.conn = DriverManager.getConnection(url, user, pwd);
			loginStatus = true;
			error=null;
		} catch (Exception e) {
			e.printStackTrace();
			loginStatus = false;
			error = "PgSQL登录异常。原因:" + e.getMessage();
		}
		return loginStatus;
	}
	
	/**
	 * 
	 * @描述: 登录HANA数据库 
	 * @param ip
	 * @param port
	 * @param dbname
	 * @param user
	 * @param pwd
	 * @return 登录状态
	 */
	public boolean loginHANA(String ip, int port, String dbName, String user, String pwd) {
		try {
			Class.forName("com.sap.db.jdbc.Driver");
			String url = "jdbc:sap://" + ip + ":" + port + "/" + dbName + "?reconnect=true";
			this.conn = DriverManager.getConnection(url, user, pwd);
			loginStatus = true;
			error=null;
		} catch (Exception e) {
			e.printStackTrace();
			loginStatus = false;
			error = "PgSQL登录异常。原因:" + e.getMessage();
		}
		return loginStatus;
	}
	
	/**
	 * 
	 * @描述: 登录oracle数据库 
	 * @param ip
	 * @param port
	 * @param dbname
	 * @param user
	 * @param pwd
	 * @return 登录状态
	 */
	public boolean loginOracle(String ip, int port, String dbname, String username, String password) {			
			try {
				Class.forName("oracle.jdbc.driver.OracleDriver");
				String conStr = "jdbc:or1 acle:thin:@//" + ip + ":" + port + "/" + dbname;
				this.conn = DriverManager.getConnection(conStr, username, password);
				loginStatus = true;
				error=null;
			} catch (Exception e) {
				try {
					String conStr = "jdbc:oracle:thin:@" + ip + ":" + port + ":" + dbname;
					this.conn = DriverManager.getConnection(conStr, username, password);
					loginStatus = true;
					error=null;
                } catch (Exception e2) {
                	System.out.println(e2);
                	error = e.getMessage();
    				this.conn = null;
    				loginStatus=false;
    				if (error.indexOf("ORA-01017:") > -1) {
    					error = "Oracle登录失败。请检查提供的用户名和密码是否正确";
    				} else if (error.indexOf("ORA-12514:") > -1) {
    					error = "Oracle登录失败。监听程序当前无法识别连接描述符中请求的服务";
    				} else {
    					error = "登录失败。原因:" + e.getMessage();
    				} 
                }
		}
		return loginStatus;
	}
	
	/**
	 * @描述: jdbc查询 
	 * @param sql 查询语句
	 * @param params  参数
	 * @return 结果的集合
	 */
	public List<Record> find(String sql, Object... params) {
		List<String> columnNames = new ArrayList<String>();
		List<Record> list = new ArrayList<Record>();
		try {
			PreparedStatement statement = conn.prepareStatement(sql);
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					statement.setObject(i + 1, params[i]);
				}
			}
			ResultSet rs = statement.executeQuery();
			ResultSetMetaData rsm = rs.getMetaData();
			int ccount = rsm.getColumnCount();
			while (rs.next()) {
				Record record = new Record();
				if (columnNames.size() == 0) {
					for (int i = 0; i < ccount; i++) {
						columnNames.add(rsm.getColumnName(i + 1));
					}
				}
				for (int i = 0; i < ccount; i++) {
					Object val = rs.getObject(i + 1);
					record.set(rsm.getColumnName(i + 1).toLowerCase(), val);
					if (val != null && val.getClass().toString().indexOf("oracle.sql.BLOB") > -1) {
						Blob blob = rs.getBlob(i + 1);
						byte[] data = null;
						InputStream in = blob.getBinaryStream();
						try {
							long nlen = blob.length();
							data = new byte[(int) nlen];
							in.read(data);
							in.close();
							record.set(rsm.getColumnName(i + 1).toLowerCase(), data);
						} catch (Exception e) {
						}
					}
				}
				list.add(record);
			}
			rs.close();
			statement.close();
			return list;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	
	/**
	 * @描述: jdbc查询 
	 * @param sql 查询语句
	 * @param params  参数
	 * @return 一个结果
	 */
	public Record findFirst(String sql, Object... params) {
		Record record = new Record();
		try {
			PreparedStatement statement = conn.prepareStatement(sql);
			if (params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					statement.setObject(i + 1, params[i]);
				}
			}
			ResultSet rs = statement.executeQuery();
			ResultSetMetaData rsm = rs.getMetaData();
			int ccount = rsm.getColumnCount();
			if (rs.next()) {
				for (int i = 0; i < ccount; i++) {
					try {
						Object val = rs.getObject(i + 1);
						record.set(rsm.getColumnName(i + 1).toLowerCase(), val);
						if (val != null && val.getClass().toString().indexOf("oracle.sql.BLOB") > -1) {
							Blob blob = rs.getBlob(i + 1);
							byte[] data = null;
							InputStream in = blob.getBinaryStream();
							try {
								long nlen = blob.length();
								data = new byte[(int) nlen];
								in.read(data);
								in.close();
								record.set(rsm.getColumnName(i + 1).toLowerCase(), data);
							} catch (Exception e) {
								// TODO: handle exception
							}
						}
					} catch (Exception e) {
						// TODO: handle exception
					}
				}
			}
			rs.close();
			statement.close();
			return record;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	
	
	/**
	 * @描述: 测试方法
	 * @param args
	 */
	public static void main(String[] args) {
	    JdbcDao jdbc = new JdbcDao();
	    jdbc.loginPgSQL("localhost", 5432, "postgres", "postgres", "root123");
	    
	}
}
